/*****************************************************************************
 *   Copyright (C) 2008 John Schember <john@nachtimwald.com>                 *
 *                                                                           *
 *   This file is part of niwbillmanager.                                    *
 *                                                                           *
 *   niwbillmanager is free software: you can redistribute it and/or         *
 *   modify it under the terms of the GNU General Public License as          *
 *   published by the Free Software Foundation, either version 3 of the      *
 *   License, or (at your option) any later version.                         *
 *                                                                           *
 *   niwbillmanager is distributed in the hope that it will be useful,       *
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of          *
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the            *
 *   GNU General Public License for more details.                            *
 *                                                                           *
 *   You should have received a copy of the GNU General Public License       *
 *   along with niwbillmanager. If not, see                                  *
 *   <http://www.gnu.org/licenses/>.                                         *
 *****************************************************************************/

#include <QFileInfo>
#include <QSet>
#include <QSqlError>
#include <QStringList>
#include <QUuid>
#include <QVariant>

#include "backendsqlite.h"

Q_EXPORT_PLUGIN2(backend_sqlite, BackendSQLite)

BackendSQLite::BackendSQLite() :
    m_readAllBillItemsQuery("SELECT id, amountdue, autopay, datedue, payee, \
        paymentlocation, paymentmethod, paymentrecurringperiod, paid, name, \
        notes, tags FROM collection;")
{
    m_db = QSqlDatabase();
    m_dbName = "";
    m_query = 0;
    m_errorMessage = "";
}

BackendSQLite::~BackendSQLite()
{
    closeCollection();
}

bool BackendSQLite::openCollection(BackendOpenMode::BackendOpenModes openMode,
    const QString &location, const QString &username, const QString &loginname,
    const QString &password)
{
    // Unused parameters that are part of the backend interface but not
    // used by this backend.
    Q_UNUSED(username);
    Q_UNUSED(loginname);
    Q_UNUSED(password);

    // We can't open directories.
    if (QFileInfo(location).isDir()) {
        m_errorMessage = "Cannot open: is Directory";
        return false;
    }

    // Setup the database connection.
    m_dbName = QUuid::createUuid().toString();
    m_db = QSqlDatabase::addDatabase("QSQLITE", m_dbName);
    m_db.setDatabaseName(location);

    if (!m_db.open()) {
        m_errorMessage = m_db.lastError().text();
        return false;
    }

    if (m_query) {
        delete m_query;
    }
    m_query = new QSqlQuery(m_db);

    if (openMode == BackendOpenMode::READ) {
        if (!execBillItemQuery("SELECT name, version FROM type;"))
        {
            m_errorMessage = "Invalid bill collection";
            return false;
        }
        if (!m_query->next()) {
            m_errorMessage = "Invalid bill collection";
            return false;
        }
        if (m_query->value(0).toString() != getName()) {
            m_errorMessage = "Invalid bill collection";
            return false;
        }
    }

    return true;
}

bool BackendSQLite::isCollectionOpen()
{
    return m_db.isOpen();
}

void BackendSQLite::closeCollection()
{
    if (m_query) {
        delete m_query;
    }
    m_query = 0;

    m_db.close();
    m_db = QSqlDatabase();
    QSqlDatabase::removeDatabase(m_dbName);
}

bool BackendSQLite::canReadIncremental()
{
    return true;
}

bool BackendSQLite::readAllBillItems(QSet<BillItem> &billItems)
{
    if (!isCollectionOpen()) {
        m_errorMessage = "No collection currently open";
        return false;
    }

    // Set the query to the first bill item.
    if (!startReadNextBillItem()) {
        return false;
    }

    // Get all of the bill items and put them in a list.
    while (hasMoreBillItems()) {
        BillItem billItem;

        readNextBillItem(billItem);

        billItems.insert(billItem);
    }

    return true;
}

bool BackendSQLite::startReadNextBillItem()
{
    if (!isCollectionOpen()) {
        m_errorMessage = "No collection currently open";
        return false;
    }

    return execReadAllBillItemsQuery();
}

bool BackendSQLite::hasMoreBillItems()
{
    // There is nothing to return if there is no collection to read from.
    if (!isCollectionOpen()) {
        return false;
    }

    // Check to see if the last query executed was the select all query.
    // We get the items from this query going though the db with query->next().
    if (m_query->lastQuery() != m_readAllBillItemsQuery) {
        return false;
    }

    // Query must be active for m_query->next() to work.
    if (!m_query->isActive()) {
        return false;
    }

    // Check to see if the next query is avaliable.
    if (!m_query->next()) {
        return false;
    }

    // Non-valid records cannt be retrieved.
    if (!m_query->isValid()) {
        return false;
    }

    // Place the recored back to where it started so readNextBillItem can
    // get the record. QSqlQuery has no peek function. We don't care if the
    // last query if valied because we will only be retrieving the next one.
    m_query->previous();

    return true;
}

bool BackendSQLite::readNextBillItem(BillItem &billItem)
{
    // This prevents us from getting non-existant data.
    if (!hasMoreBillItems()) {
        return false;
    }

    // Grab the next item.
    if (!m_query->next()) {
        return false;
    }

    // The next series of statements gets the value from the query and puts it
    // in the proper place in the bill item. The value(NUMBER) corresponds to
    // the position of the query item after SELECT in m_readAllBillItemsQuery.

    if (!m_query->value(0).toString().isEmpty()) {
        billItem.setId(m_query->value(0).toString());
    }

    if (!m_query->value(1).toString().isEmpty()) {
        billItem.setAmountDue(m_query->value(1).toDouble());
    }

    if (!m_query->value(2).toString().isEmpty()) {
        billItem.setAutoPay(m_query->value(2).toString()=="T"?true:false);
    }

    if (!m_query->value(3).toString().isEmpty()) {
        billItem.setDateDue(QDate::fromString(
            m_query->value(3).toString(), Qt::ISODate));
    }

    if (!m_query->value(4).toString().isEmpty()) {
        billItem.setPayee(m_query->value(4).toString());
    }

    if (!m_query->value(5).toString().isEmpty()) {
        billItem.setPaymentLocation(m_query->value(5).toString());
    }

    if (!m_query->value(6).toString().isEmpty()) {
        billItem.setPaymentMethod(m_query->value(6).toString());
    }

    if (!m_query->value(7).toString().isEmpty()) {
        billItem.setPaymentRecurringPeriod(
            RecurringPeriodType::stringToRecurringPeriodType(
            m_query->value(7).toString()));
    }

    if (!m_query->value(8).toString().isEmpty()) {
        billItem.setPaid(m_query->value(8).toString()=="T"?true:false);
    }

    if (!m_query->value(9).toString().isEmpty()) {
        billItem.setName(m_query->value(9).toString());
    }

    if (!m_query->value(10).toString().isEmpty()) {
        billItem.setNotes(m_query->value(10).toString());
    }

    if (!m_query->value(11).toString().isEmpty()) {
        billItem.setTags(m_query->value(11).toString().split(" ").toSet());
    }

    return true;
}

bool BackendSQLite::saveAllBillItems(QSet<BillItem> billItems)
{
    if (!isCollectionOpen()) {
        m_errorMessage = "No collection currently open";
        return false;
    }

    if (!createNewDb()) {
        return false;
    }

    Q_FOREACH(BillItem billItem, billItems)
    {
        // insert all of the data into the table.
        if (!execInsertBillItemQuery(billItem)) {
            return false;
        }
    }

    return true;
}

bool BackendSQLite::canSaveTransactional()
{
    return true;
}

bool BackendSQLite::canSaveAllIncremental()
{
    return false;
}

bool BackendSQLite::saveChange(Transaction transaction)
{
    if (!isCollectionOpen()) {
        m_errorMessage = "No collection currently open";
        return false;
    }

    if (!isDbVaild()) {
        createNewDb();
    }

    switch (transaction.getTransactionType())
    {
        case TransactionType::INSERT:
            if (!execInsertBillItemQuery(transaction.getBillItem())) {
                return false;
            }
            break;
        case TransactionType::UPDATE:
            if (!execUpdateBillItemQuery(transaction.getBillItem())) {
                return false;
            }
            break;
        case TransactionType::DELETE:
            if (!execDeleteBillItemQuery(transaction.getBillItem()))
            {
                return false;
            }
            break;
        default:
            break;
    }

    return true;
}


bool BackendSQLite::saveBillItem(const BillItem &billItem)
{
    // This is not available in this backend.
    Q_UNUSED(billItem);
    return false;
}

QString BackendSQLite::getLastErrorMessage()
{
    return m_errorMessage;
}

// This backend does not use remote connections. It writes to a local file.
bool BackendSQLite::isRemote()
{
    return false;
}

QString BackendSQLite::getName()
{
    return "SQLite";
}

QString BackendSQLite::getDescription()
{
    return "Saves to a file that is a mini SQL database.";
}

QString BackendSQLite::getVersion()
{
    return "1.0.1";
}

QString BackendSQLite::getDefaultFileExtenstion()
{
    return "db";
}

bool BackendSQLite::execCreateEmptyTableQuery()
{
    return execBillItemQuery("CREATE TABLE collection \
        (id integer primary key, amountdue REAL,  autopay TEXT, \
        datedue TEXT, payee TEXT, paymentlocation TEXT, paymentmethod TEXT, \
        paymentrecurringperiod TEXT, paid TEXT, name TEXT, notes TEXT, \
        tags TEXT);");
}

bool BackendSQLite::execCreateTypeMarkerQuery()
{
    if (!execBillItemQuery("CREATE TABLE type (name TEXT, version TEXT);")) {
        return false;
    }
    if (!execBillItemQuery("INSERT INTO type (name, version) values("
        "'" + getName() + "', "
        "'" + getVersion() + "'"
        ");"))
    {
        return false;
    }

    return true;
}

bool BackendSQLite::execDropTableQuery()
{
    return execBillItemQuery("DROP TABLE IF EXISTS collection;");
}

bool BackendSQLite::execReadAllBillItemsQuery()
{
    return execBillItemQuery(m_readAllBillItemsQuery);
}

bool BackendSQLite::execInsertBillItemQuery(const BillItem &billItem)
{
    if (!execBillItemQuery("INSERT INTO collection (id, amountdue, \
            autopay, datedue, payee, paymentlocation, paymentmethod, \
            paymentrecurringperiod, paid, name, notes, tags) values("
            "'" + billItem.getId() + "', "
            "'" + QString::number(billItem.getAmountDue()) + "', "
            "'" + (billItem.getAutoPay()?"T":"F") + "', "
            "'" + billItem.getDateDue().toString(Qt::ISODate) + "', "
            "'" + billItem.getPayee() + "', "
            "'" + billItem.getPaymentLocation() + "', "
            "'" + billItem.getPaymentMethod() + "', "
            "'" + RecurringPeriodType::recurringPeriodTypeToString(
                billItem.getPaymentRecurringPeriod()) + "', "
            "'" + (billItem.getPaid()?"T":"F") + "', "
            "'" + billItem.getName() + "', "
            "'" + billItem.getNotes() + "', "
            "'" + BillItem::stringSetToString(billItem.getTags()) + "'"
            ");"))
        {
            m_errorMessage = m_query->lastError().text();
            return false;
        }

    return true;
}

bool BackendSQLite::execUpdateBillItemQuery(const BillItem &billItem)
{
    if (!isBillItemIdValid(billItem)) {
        return false;
    }

    if (!execBillItemQuery("UPDATE collection SET "
            "amountdue='" + QString::number(billItem.getAmountDue()) + "', "
            "autopay='" + (billItem.getAutoPay()?"T":"F") + "', "
            "datedue='" + billItem.getDateDue().toString(Qt::ISODate) + "', "
            "payee='" + billItem.getPayee() + "', "
            "paymentlocation='" + billItem.getPaymentLocation() + "', "
            "paymentmethod='" + billItem.getPaymentMethod() + "', "
            "paymentrecurringperiod='" +
                RecurringPeriodType::recurringPeriodTypeToString(
                billItem.getPaymentRecurringPeriod()) + "', "
            "paid='" + (billItem.getPaid()?"T":"F") + "', "
            "name='" + billItem.getName() + "', "
            "notes='" + billItem.getNotes() + "', "
            "tags='" + BillItem::stringSetToString(billItem.getTags()) + "' "
            "WHERE id='" + billItem.getId() + "'"
            ";"))
        {
            m_errorMessage = m_query->lastError().text();
            return false;
        }

    return true;
}


bool BackendSQLite::execDeleteBillItemQuery(const BillItem &billItem)
{
    if (!isBillItemIdValid(billItem)) {
        return false;
    }

    return execBillItemQuery("DELETE FROM collection where id='" +
        billItem.getId() + "';");
}

bool BackendSQLite::execBillItemQuery(const QString &query)
{
    // Check if the collection is open. The query is attached to an open m_db
    // and cannot run if there db is not valid.
    if (!isCollectionOpen()) {
        m_errorMessage = "No collection currently open";
        return false;
    }

    // Execute the query setting any errors if unsuccessful.
    if (!m_query->exec(query))
    {
        m_errorMessage = m_query->lastError().text();
        return false;
    }

    return true;
}

bool BackendSQLite::isDbVaild()
{
    if (!isCollectionOpen()) {
        m_errorMessage = "No collection currently open";
        return false;
    }

    return m_db.tables().contains("collection");
}

bool BackendSQLite::createNewDb()
{
    if (!isCollectionOpen()) {
        m_errorMessage = "No collection currently open";
        return false;
    }

    // Remove any data in the file so we can save the sqlite db into it.
    if (QFileInfo(m_db.databaseName()).exists()) {
        QFile(m_db.databaseName()).resize(0);
    }

    // Delete any data in the db if any exists.
    if (!execDropTableQuery()) {
        return false;
    }

    // Try and create the table to save the data in.
    if (!execCreateEmptyTableQuery()) {
        return false;
    }
    if (!execCreateTypeMarkerQuery()) {
        return false;
    }

    return true;
}

bool BackendSQLite::isBillItemIdValid(const BillItem &billItem)
{
    if (billItem.getId().isEmpty()) {
        m_errorMessage = "Invalid bill item: " + billItem.getName() +
            " has no id";
        return false;
    }

    return true;
}
